﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using MySql.Data.MySqlClient;
using MySql.Data;
using System.Threading;
using MetaSearcher;
namespace MetaSearcher
{
    class Global2
    {
        public String[] time = { "00:00-04:00", "04:00-08:00", "08:00-12:00", "12:00-16:00", "16:00-20:00", "20:00-00:00" };
        public int[] price = new int[6];
    }
    class Statistic2
    {
        public Global2 getDataForStat2(string resource, string city_from, string city_to, DateTime date_start, DateTime date_finish, int frequency)
        {
            string Connect = "Database=meta;Data Source=188.35.9.179;User Id=assa;Password=assa";
            int price = 0;
            DateTime date_dep = new DateTime(0001, 01, 01, 00, 00, 00);
            Global2 gl_stat = new Global2();
            string getRecNum = "select count(*) from main where resource=" + "'" + resource + "'" + " and city_from=" + "'" + city_from + "'" + " and city_to=" + "'" + city_to + "'" + " and (SELECT CAST(date_dep AS DATE)) >= " + "'" + date_start.ToString("yyyy-MM-dd") + "'" + " and (SELECT CAST(date_dep AS DATE)) <= " + "'" + date_finish.ToString("yyyy-MM-dd") + "'" + " and (SELECT CAST(date_collect AS DATE)) = (SELECT CAST(date_dep AS DATE))" + ";";
            int cou = 0;
            MySqlConnection myConnection0 = new MySqlConnection(Connect);
            MySqlCommand getRec = new MySqlCommand(getRecNum, myConnection0);
            try
            {
                myConnection0.Open();

                MySqlDataReader countreader = getRec.ExecuteReader();
                countreader.Read();
                cou = countreader.GetInt32(0);
                countreader.Close();
                myConnection0.Close();
            }
            catch (Exception ee) { int er1 = 1; }
            
            if (cou == 0)
            {
                Console.WriteLine("No data: returning null");
                return null;
            }
            else
            {
                //Заполнение массивов выходных данных
                int pricesum1 = 0, pricesum2 = 0, pricesum3 = 0, pricesum4 = 0, pricesum5 = 0, pricesum6 = 0;
                int pricecount1 = 0, pricecount2 = 0, pricecount3 = 0, pricecount4 = 0, pricecount5 = 0, pricecount6 = 0;
                int price_min1 = 0, price_min2 = 0, price_min3 = 0, price_min4 = 0, price_min5 = 0, price_min6 = 0;
                int flag1 = 0, flag2 = 0, flag3 = 0, flag4 = 0, flag5 = 0, flag6 = 0;
                int inc = 1;
                DateTime date_watch;
                date_watch = date_start;
                while (date_watch <= date_finish)
                {
                    string getQuery = "select * from main where resource=" + "'" + resource + "'" + " and city_from=" + "'" + city_from + "'" + " and city_to=" + "'" + city_to + "'" + " and (SELECT CAST(date_dep AS DATE))=" + "'" + date_watch.ToString("yyyy-MM-dd") + "'" + " and (SELECT CAST(date_collect AS DATE))=(SELECT CAST(date_dep AS DATE))" + ";";
                    MySqlConnection myConnection = new MySqlConnection(Connect);
                    MySqlCommand getQue = new MySqlCommand(getQuery, myConnection);
                    int hh = 0;
                    hh = (new DBManipulation()).getRecordNumber(Connect, "main", "where resource=" + "'" + resource + "'" + " and city_from=" + "'" + city_from + "'" + " and city_to=" + "'" + city_to + "'" + " and (SELECT CAST(date_dep AS DATE))=" + "'" + date_watch.ToString("yyyy-MM-dd") + "'" + " and (SELECT CAST(date_collect AS DATE))=(SELECT CAST(date_dep AS DATE))");

                    try
                    {

                        //hh = (new DBManipulation()).getRecordNumber(Connect, "main", "where resource=" + "'" + resource + "'" + " and city_from=" + "'" + city_from + "'" + " and city_to=" + "'" + city_to + "'" + " and (SELECT CAST(date_dep AS DATE))=" + "'" + date_watch.ToString("yyyy-MM-dd") + "'" + " and (SELECT CAST(date_collect AS DATE))=(SELECT CAST(date_dep AS DATE))");

                        if (hh != 0)
                        {

                            myConnection.Open();
                            MySqlDataReader mreader = getQue.ExecuteReader();
                            while (mreader.Read())
                            {
                                price = mreader.GetInt32(2);
                                date_dep = DateTime.Parse(mreader.GetString(5));

                                if (0 <= date_dep.Hour && date_dep.Hour < 4)
                                {
                                    if (flag1 == 0)
                                    {
                                        price_min1 = price;
                                        flag1 = 1;
                                    }
                                    else
                                    {
                                        if (price < price_min1) price_min1 = price;
                                    }
                                }
                                if (4 <= date_dep.Hour && date_dep.Hour < 8)
                                {
                                    if (flag2 == 0)
                                    {
                                        price_min2 = price;
                                        flag2 = 1;
                                    }
                                    else
                                    {
                                        if (price < price_min2) price_min2 = price;
                                    }
                                }
                                if (8 <= date_dep.Hour && date_dep.Hour < 12)
                                {
                                    if (flag3 == 0)
                                    {
                                        price_min3 = price;
                                        flag3 = 1;
                                    }
                                    else
                                    {
                                        if (price < price_min3) price_min3 = price;
                                    }
                                }
                                if (12 <= date_dep.Hour && date_dep.Hour < 16)
                                {
                                    if (flag4 == 0)
                                    {
                                        price_min4 = price;
                                        flag4 = 1;
                                    }
                                    else
                                    {
                                        if (price < price_min4) price_min4 = price;
                                    }
                                }
                                if (16 <= date_dep.Hour && date_dep.Hour < 20)
                                {
                                    if (flag5 == 0)
                                    {
                                        price_min5 = price;
                                        flag5 = 1;
                                    }
                                    else
                                    {
                                        if (price < price_min5) price_min5 = price;
                                    }
                                }
                                if (20 <= date_dep.Hour && date_dep.Hour <= 23 && date_dep.Minute <= 59)
                                {
                                    if (flag6 == 0)
                                    {
                                        price_min6 = price;
                                        flag6 = 1;
                                    }
                                    else
                                    {
                                        if (price < price_min6) price_min6 = price;
                                    }
                                }
                            }

                            mreader.Close();
                        }

                        myConnection.Close();


                    }
                    catch (Exception ee) { int er2 = 1; Console.WriteLine(ee.ToString()); }

                    if (0 <= date_dep.Hour && date_dep.Hour < 4)
                    {
                        pricesum1 += price_min1;
                        pricecount1++;
                    }
                    if (4 <= date_dep.Hour && date_dep.Hour < 8)
                    {
                        pricesum2 += price_min2;
                        pricecount2++;
                    }
                    if (8 <= date_dep.Hour && date_dep.Hour < 12)
                    {
                        pricesum3 += price_min3;
                        pricecount3++;
                    }
                    if (12 <= date_dep.Hour && date_dep.Hour < 16)
                    {
                        pricesum4 += price_min4;
                        pricecount4++;
                    }
                    if (16 <= date_dep.Hour && date_dep.Hour < 20)
                    {
                        pricesum5 += price_min5;
                        pricecount5++;
                    }
                    if (16 <= date_dep.Hour && date_dep.Hour <= 23 && date_dep.Minute <= 59)
                    {
                        pricesum6 += price_min6;
                        pricecount6++;
                    }
                    price_min1 = 0; price_min2 = 0; price_min3 = 0; price_min4 = 0; price_min5 = 0; price_min6 = 0;
                    flag1 = 0; flag2 = 0; flag3 = 0; flag4 = 0; flag5 = 0; flag6 = 0;
                    date_watch = date_start.AddDays(frequency * inc);
                    inc++;

                }
                try
                {

                    gl_stat.price[0] = (int)pricesum1 / pricecount1;
                    gl_stat.price[1] = (int)pricesum2 / pricecount2;
                    gl_stat.price[2] = (int)pricesum3 / pricecount3;
                    gl_stat.price[3] = (int)pricesum4 / pricecount4;
                    gl_stat.price[4] = (int)pricesum5 / pricecount5;
                    gl_stat.price[5] = (int)pricesum6 / pricecount6;
                }
                catch (DivideByZeroException e) { Console.WriteLine("ZeroDivide"); }
                return gl_stat;
            }
        }
        public void formStatistic2(String Connect, int id_user)
        {
            DBManipulation m2 = new DBManipulation();
            MySqlConnection metaConnection = new MySqlConnection(Connect);
            int id_stat = m2.getRecordNumber(Connect, "statistic", "") + 1;
            int count_to = 0;
            int count_from = 0;
            String axis_x = "";
            String axis_y = "";
            String[] route;
            int goodcount = 0;
            int[] id_req;
            int k = m2.getRecordNumber(Connect, "request_user", "where id_user=" + id_user);
            if (k == 0)
            {
                Console.WriteLine("User " + id_user + " has no requests.");
            }
            else
            {
                id_req = new int[k];
                String selrequs = "select id_request from request_user where id_user=" + id_user + ";";
                MySqlCommand selectreqforuser = new MySqlCommand(selrequs, metaConnection);
                MySqlDataReader mread;
                try
                {
                    int gh = 0;
                    metaConnection.Open();
                    mread = selectreqforuser.ExecuteReader();
                    while (mread.Read())
                    {
                        id_req[gh] = mread.GetInt32(0);
                        gh++;
                    }
                    metaConnection.Close();
                }
                catch (Exception ee) { Console.WriteLine("Failed:StatisticMethod   " + ee.ToString()); Console.ReadKey(); }
                //int reqcount = getRecordNumber(Connect, "request", "where id_request=(select id_request from request_user where id_user=" + id_user + ") and status=2 and statistic=\"type4\";");

                //Console.WriteLine("Strok: "+reqcount);
                String searchPred = "where (id_request=";
                for (int l = 0; l < id_req.Length; l++)
                {
                    searchPred += "" + id_req[l] + " or id_request=";
                }
                searchPred += "" + id_req[0];
                searchPred += ") and status=2 and statistic=\"2type\"";
                searchPred += ";";

                RequestPattern[] status2Request = m2.selectFromRequest(Connect, searchPred);

                Global2 goodGlob = new Global2();
                for (int i = 0; i < status2Request.Length; i++)
                {

                    while (count_from < status2Request[i].city_from.Length)
                    {

                        while (count_to < status2Request[i].city_to.Length)
                        {

                            if (status2Request[i].city_from.Substring(count_from, 3).Equals(status2Request[i].city_to.Substring(count_to, 3)) == false)
                                goodcount++;

                            count_to += 3;

                        }
                        count_to = 0;
                        count_from += 3;
                    }
                    count_to = 0;
                    count_from = 0;
                    route = new String[5];
                    int route_count = 0;
                    while (count_from < status2Request[i].city_from.Length)
                    {
                        while (count_to < status2Request[i].city_to.Length)
                        {
                            if (status2Request[i].city_from.Substring(count_from, 3).Equals(status2Request[i].city_to.Substring(count_to, 3)) == false)
                            {


                                route[route_count] = "" + status2Request[i].city_from.Substring(count_from, 3) + "" + status2Request[i].city_to.Substring(count_to, 3) + "";

                                goodGlob = this.getDataForStat2(status2Request[i].resource, status2Request[i].city_from.Substring(count_from, 3), status2Request[i].city_to.Substring(count_to, 3), status2Request[i].day_start, status2Request[i].day_finish, status2Request[i].frequency);
                                if (goodGlob == null)
                                {
                                    Console.WriteLine("Global2:null");
                                }
                                else
                                {
                                    for (int j = 0; j < 6; j++)
                                    {
                                        axis_x += "" + goodGlob.time[j] + ";";
                                        axis_y += "" + goodGlob.price[j] + ";";
                                        Console.WriteLine(goodGlob.time[j]);
                                        Console.WriteLine(goodGlob.price[j]);
                                    }


                                    String ins_req_stat = "insert into request_statistic(id_request, id_statistic) values (" + status2Request[i].id_request + ", " + id_stat + ");";

                                    String ins_stat = "insert into statistic(id_statistic, route, axis_x, axis_y) values (" + id_stat + ", \"" + route[route_count] + "\", \"" + axis_x + "\", \"" + axis_y + "\");";

                                    MySqlCommand insert_r_s = new MySqlCommand(ins_req_stat, metaConnection);
                                    MySqlCommand insert_s = new MySqlCommand(ins_stat, metaConnection);
                                    try
                                    {
                                        metaConnection.Open();
                                        insert_s.ExecuteNonQuery();
                                        insert_r_s.ExecuteNonQuery();
                                        axis_x = "";
                                        axis_y = "";
                                        metaConnection.Close();
                                    }
                                    catch (Exception ee) { Console.WriteLine("Failed:StatisticMethod   " + ee.ToString()); Console.ReadKey(); }
                                    route_count++;
                                    id_stat++;
                                }
                            }
                            count_to += 3;
                        }
                        count_to = 0;
                        count_from += 3;

                    }
                }
            }

        }
        
    }
}
